﻿#include "mainwindow.h"
#include "ui_mainwindow.h"

#include    <QFileDialog>
#include    <QMessageBox>
void MainWindow::openTable()
{
    tabModel = new QSqlTableModel(this, DB); //数据表
    tabModel->setTable("employee"); //读取数据
    //Qt::AscendingOrder 升序
    //Qt::DescendingOrder 降序
    tabModel->setSort(tabModel->fieldIndex("EmpNo"),Qt::AscendingOrder); //升序
    //OnManualSubmit  所有修改暂时缓存，手动调用 submitAll（）保存所有修改 ， 或调用 revertAll （）函数取消所有未保存修改。
    //OnRowChange  当前行（就是记录）变换时更新到数据库 。
    //OnFieldChange 字段值变化时立即更新到数据库 。
    tabModel->setEditStrategy(QSqlTableModel::OnManualSubmit);

    if(!(tabModel->select())) //查询数据库
    {
       QMessageBox::critical(this,"错误","打开数据表错误,错误信息\n"+
                             tabModel->lastError().text(),
                             QMessageBox::Ok,QMessageBox::NoButton);
       return;
    }
    //设置字段显示名
    tabModel->setHeaderData(tabModel->fieldIndex("empNo"),Qt::Horizontal,"工号");
    tabModel->setHeaderData(tabModel->fieldIndex("Name"),Qt::Horizontal,"姓名");
    tabModel->setHeaderData(tabModel->fieldIndex("Gender"),Qt::Horizontal,"性别");
    tabModel->setHeaderData(tabModel->fieldIndex("Height"),Qt::Horizontal,"身高");
    tabModel->setHeaderData(tabModel->fieldIndex("Birthday"),Qt::Horizontal,"出生日期");
    tabModel->setHeaderData(tabModel->fieldIndex("Mobile"),Qt::Horizontal,"手机");
    tabModel->setHeaderData(tabModel->fieldIndex("Province"),Qt::Horizontal,"省份");
    tabModel->setHeaderData(tabModel->fieldIndex("City"),Qt::Horizontal,"城市");
    tabModel->setHeaderData(tabModel->fieldIndex("Department"),Qt::Horizontal,"部门");
    tabModel->setHeaderData(tabModel->fieldIndex("Education"),Qt::Horizontal,"学历");
    tabModel->setHeaderData(tabModel->fieldIndex("Salary"),Qt::Horizontal,"工资");

    tabModel->setHeaderData(tabModel->fieldIndex("Memo"),Qt::Horizontal,"备注"); //这两个字段不再tableView中显示
    tabModel->setHeaderData(tabModel->fieldIndex("Photo"),Qt::Horizontal,"照片");

    theSelection = new QItemSelectionModel(tabModel); //选择模型
//theSelection当前项变化时触发currentChanged信号
    connect(theSelection,SIGNAL(currentChanged(QModelIndex,QModelIndex)),
            this,SLOT(on_currentChanged(QModelIndex,QModelIndex)));
//选择行变化时
    connect(theSelection,SIGNAL(currentRowChanged(QModelIndex,QModelIndex)),
            this,SLOT(on_currentRowChanged(QModelIndex,QModelIndex)));

    ui->tableView->setModel(tabModel); //设置数据模型
    ui->tableView->setSelectionModel(theSelection);//设置选择模型
    ui->tableView->setColumnHidden(tabModel->fieldIndex("Memo"),true);//隐藏列
    ui->tableView->setColumnHidden(tabModel->fieldIndex("Photo"),true);//隐藏列

//tableView上为“性别”和“部门”两个字段设置自定义代理组件
    QStringList strList;
    strList << "男" << "女";
    bool isEditable = false;
    delegateSex.setItems(strList,isEditable);
    ui->tableView->setItemDelegateForColumn(
                tabModel->fieldIndex("Gender"),&delegateSex);

    strList.clear();
    strList << "销售部"<<"技术部"<<"生产部"<<"行政部";
    isEditable = true;
    delegateDepart.setItems(strList,isEditable);
    ui->tableView->setItemDelegateForColumn(tabModel->fieldIndex("Department"),&delegateDepart); //Combbox选择型

    //创建界面组件与数据模型的字段之间的数据映射
    dataMapper = new QDataWidgetMapper();
    dataMapper->setModel(tabModel);//设置数据模型
    dataMapper->setSubmitPolicy(QDataWidgetMapper::AutoSubmit);//自动提交
    //  dataMapper->setItemDelegate(new QSqlRelationalDelegate(this)); //含有外键的
    //界面组件与tabModel的具体字段之间的联系
    dataMapper->addMapping(ui->dbSpinEmpNo,tabModel->fieldIndex("empNo"));
    dataMapper->addMapping(ui->dbEditName,tabModel->fieldIndex("Name"));
    dataMapper->addMapping(ui->dbComboSex,tabModel->fieldIndex("Gender"));

    dataMapper->addMapping(ui->dbSpinHeight,tabModel->fieldIndex("Height"));
    dataMapper->addMapping(ui->dbEditBirth,tabModel->fieldIndex("Birthday"));
    dataMapper->addMapping(ui->dbEditMobile,tabModel->fieldIndex("Mobile"));

    dataMapper->addMapping(ui->dbComboProvince,tabModel->fieldIndex("Province"));
    dataMapper->addMapping(ui->dbEditCity,tabModel->fieldIndex("City"));
    dataMapper->addMapping(ui->dbComboDep,tabModel->fieldIndex("Department"));

    dataMapper->addMapping(ui->dbComboEdu,tabModel->fieldIndex("Education"));
    dataMapper->addMapping(ui->dbSpinSalary,tabModel->fieldIndex("Salary"));

    dataMapper->addMapping(ui->dbEditMemo,tabModel->fieldIndex("Memo"));

    dataMapper->toFirst();//移动到首记录
    //获取字段名称列表，填充ui->groupBoxSort组件
    getFieldNames();

    //更新actions和界面组件的使能状态
     ui->actOpenDB->setEnabled(false);

     ui->actRecAppend->setEnabled(true);
     ui->actRecInsert->setEnabled(true);
     ui->actRecDelete->setEnabled(true);
     ui->actScan->setEnabled(true);

     ui->groupBoxSort->setEnabled(true);
     ui->groupBoxFilter->setEnabled(true);

}

void MainWindow::getFieldNames()
{
    //获取所有字段名称
    QSqlRecord emptyRec = tabModel->record(); //获取空记录，只有字段名
    for (int i=0;i<emptyRec.count();i++)
        ui->comboFields->addItem(emptyRec.fieldName(i));
}

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    this->setCentralWidget(ui->splitter); //以这个为中心
    //   tableView显示属性设置
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectItems); //选中方式
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection); //可以单选
    ui->tableView->setAlternatingRowColors(true); //可以设置颜色

}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::on_currentChanged(const QModelIndex &current, const QModelIndex &previous)
{
    //更新actPost和actCancel 的状态
        Q_UNUSED(current);
        Q_UNUSED(previous);
     ui->actSubmit->setEnabled(tabModel->isDirty());//有未保存修改时可用
     ui->actRevert->setEnabled(tabModel->isDirty());
}

void MainWindow::on_currentRowChanged(const QModelIndex &current, const QModelIndex &previous)
{
    Q_UNUSED(previous);
// 行切换时的状态控制
    ui->actRecDelete->setEnabled(current.isValid());
    ui->actPhoto->setEnabled(current.isValid());
    ui->actPhotoClear->setEnabled(current.isValid());

    if (!current.isValid())
    {
        ui->dbLabPhoto->clear(); //清除图片显示
        return;
    }

    dataMapper->setCurrentIndex(current.row()); //更细数据映射的行号
    int curRecNo = current.row();//获取行号

    QSqlRecord  curRec=tabModel->record(curRecNo); //获取当前记录
    if(curRec.isNull("Photo"))
    {
        ui->dbLabPhoto->clear();
    }
    else {
        QByteArray data = curRec.value("Photo").toByteArray();
        QPixmap pic;
        pic.loadFromData(data);
        ui->dbLabPhoto->setPixmap(pic.scaledToWidth(ui->dbLabPhoto->size().width()));
    }

}

void MainWindow::on_actOpenDB_triggered()
{
    QString aFile = QFileDialog::getOpenFileName(this,"选择数据库文件","",
                                                 "SQL Lite数据库(*.db *.db3)");
    if(aFile.isEmpty())  //未选中目标文件
    {
        return;
    }

    DB = QSqlDatabase::addDatabase("QSQLITE"); //加载 SQL LITE数据库驱动
    DB.setDatabaseName(aFile);  //设置数据库名称

    if(!DB.open())
    {
        QMessageBox::warning(this,"错误", "打开数据库失败",
                             QMessageBox::Ok,QMessageBox::NoButton);
        return;
    }

    //打开数据表
    openTable();
}

void MainWindow::on_actRecAppend_triggered()
{
    //添加记录
        tabModel->insertRow(tabModel->rowCount(),QModelIndex()); //在末尾添加一个记录

        QModelIndex curIndex=tabModel->index(tabModel->rowCount()-1,1);//创建最后一行的ModelIndex
        theSelection->clearSelection();//清空选择项
        theSelection->setCurrentIndex(curIndex,QItemSelectionModel::Select);//设置刚插入的行为当前选择行

        int currow=curIndex.row(); //获得当前行
        tabModel->setData(tabModel->index(currow,0),2000+tabModel->rowCount()); //自动生成编号
        tabModel->setData(tabModel->index(currow,2),"男");
    // 插入行时设置缺省值，需要在primeInsert()信号里去处理
}


void MainWindow::on_actRecInsert_triggered()
{
     QModelIndex curIndex = ui->tableView->currentIndex();

     tabModel->insertRow(curIndex.row(),QModelIndex());
     theSelection->clearSelection();//清除已有选择
     theSelection->setCurrentIndex(curIndex,QItemSelectionModel::Select);//设置刚插入的行为当前选择行
}



void MainWindow::on_actRecDelete_triggered()
{
     QModelIndex curIndex =  ui->tableView->currentIndex();
     tabModel->removeRow(curIndex.row());

     //    if (curIndex.row()==tabModel->rowCount()-1)//最后一行
     //        tabModel->removeRow(curIndex.row()); //删除最后一行
     //    else
     //    {
     //        tabModel->removeRow(curIndex.row());//删除一行，并重新设置当前选择行
     //        theSelection->setCurrentIndex(curIndex,QItemSelectionModel::Select);
     //    }
     //    tabModel->submitAll(); //立即更新
}

void MainWindow::on_actSubmit_triggered()
{
    //保存修改
    bool res = tabModel->submitAll();
    if(!res)
    {
        QMessageBox::information(this,"消息","数据保存错误,错误信息\n"+tabModel->lastError().text(),
                                 QMessageBox::Ok,QMessageBox::NoButton);
    }
    else
    {
        ui->actSubmit->setEnabled(false);
        ui->actRevert->setEnabled(false);
    }
}

void MainWindow::on_actRevert_triggered()
{
    //取消修改
     tabModel->revertAll();
     ui->actSubmit->setEnabled(false);
     ui->actRevert->setEnabled(false);
}

void MainWindow::on_actPhoto_triggered()
{
    //设置图片
    QString aFile=QFileDialog::getOpenFileName(this,"选择图片文件","","照片(*.jpg)");
    if (aFile.isEmpty())
       return;
    QByteArray data;
    QFile* file=new QFile(aFile); //fileName为二进制数据文件名
    file->open(QIODevice::ReadOnly);
    data = file->readAll();
    file->close();

    int curRecNo=theSelection->currentIndex().row(); //获取选择的行
    QSqlRecord  curRec=tabModel->record(curRecNo); //获取当前记录
    curRec.setValue("Photo",data); //设置字段数据
    tabModel->setRecord(curRecNo,curRec); //使用这个设置图片
//    tabModel->submit(); //并不能保存到数据库

    QPixmap pic;
    pic.load(aFile);  //在界面上显示
    ui->dbLabPhoto->setPixmap(pic.scaledToWidth(ui->dbLabPhoto->width()));
}

void MainWindow::on_actPhotoClear_triggered()
{
    int curRecNo = theSelection->currentIndex().row(); //获取选择的一行
    QSqlRecord curRec = tabModel->record(curRecNo); //获取当前记录

    curRec.setNull("Photo");//设置为空值
    tabModel->setRecord(curRecNo,curRec);

    ui->dbLabPhoto->clear(); //清除图片
}

void MainWindow::on_radioBtnAscend_clicked()
{
    //升序
    tabModel->setSort(ui->comboFields->currentIndex(),Qt::AscendingOrder);
    tabModel->select();
}


void MainWindow::on_radioBtnDescend_clicked()
{
    //降序
    tabModel->setSort(ui->comboFields->currentIndex(),Qt::DescendingOrder);
    tabModel->select();
}


void MainWindow::on_radioBtnMan_clicked()
{
    tabModel->setFilter(" Gender='男' ");
}

void MainWindow::on_radioBtnMan_2_clicked()
{
    tabModel->setFilter(" Gender='女' ");
}

void MainWindow::on_radioBtnBoth_clicked()
{
    tabModel->setFilter("");
}


void MainWindow::on_comboFields_currentIndexChanged(int index)
{
    //根据字段进行排序
    if(ui->radioBtnAscend->isChecked())
    {
        tabModel->setSort(index,Qt::AscendingOrder);
    }
    else
    {
        tabModel->setSort(index,Qt::DescendingOrder);
    }
    tabModel->select();
}

void MainWindow::on_actScan_triggered()
{
    //涨工资，记录遍历
    if (tabModel->rowCount()==0)
        return;
    for (int i = 0;i <tabModel->rowCount();i++)
    {
        QSqlRecord aRec = tabModel->record(i);//获取当前记录
        float salary = aRec.value("Salary").toFloat();
        salary *= 1.1f;
        aRec.setValue("Salary",salary);
        tabModel->setRecord(i,aRec);
    }

    if(tabModel->submitAll())
    {
        QMessageBox::information(this,"消息","涨工资计算完毕",
                              QMessageBox::Ok,QMessageBox::NoButton);
    }
}
